# -*- coding: utf-8 -*-
"""
Created on Mon Jul 24 11:30:24 2023

@author: wangwenjie
"""

import numpy as np
import pandas as pd
import datetime
import os
import re
import pymysql
import Dfactor_calculate
from sqlalchemy import create_engine
import rqdatac
from WindPy import w
w.start()

path = os.getcwd()

def df_data(sheet_name,start, end):  # 通过excel提取因子数据
    df = pd.read_excel(os.path.join(path, 'param/副本factor_test.xlsx'), sheet_name = sheet_name)
    df = df[(df.t_date>=pd.to_datetime(start)) & (df.t_date<=pd.to_datetime(end))]
    return df

def df_factor_data(factor_name,start, end, freq):  # 通过函数提取因子数据
    lst = {
           'sec_convprice':'Dfactor_calculate.convfactor_calculation(start, end, freq).fconvprice()',                      # 转债价格
           'sec_convsize':'Dfactor_calculate.convfactor_calculation(start, end, freq).fconvsize()',                        # 转债余额
           'sec_conv_stocksize':'Dfactor_calculate.convfactor_calculation(start, end, freq).fconv_stocksize()',            # 转债余额占正股市值比重
           'sec_ytm':'Dfactor_calculate.convfactor_calculation(start, end, freq).fytm_cb()',                               # 到期收益率
           'sec_bond_premium':'Dfactor_calculate.convfactor_calculation(start, end, freq).fbondPremiumRatio()',            # 纯债溢价率
           'sec_IV_BS':'Dfactor_calculate.convfactor_calculation(start, end, freq).fIV_BS()',                              # 隐含波动率_BS
           'sec_IV_delta':'Dfactor_calculate.convfactor_calculation(start, end, freq).fIV_difference()',                   # 隐波差
           'sec_weightskew_63D':'Dfactor_calculate.convfactor_calculation(start, end, freq).fweight_skew(ND=63)',          # 63日转债价格偏度因子
           'sec_convturnover':'Dfactor_calculate.convfactor_calculation(start, end, freq).fconvChange()',                  # 换手率
           'sec_volatility5d':'Dfactor_calculate.convfactor_calculation(start, end, freq).fpct_std(ND=5)',                 # 5日波动率
           'sec_conv_amplitude':'Dfactor_calculate.convfactor_calculation(start, end, freq).fconv_amplitude()',            # 振幅
           'sec_volstability_10d':'Dfactor_calculate.convfactor_calculation(start, end, freq).fvol_firm(ND=10)',           # 10日成交量稳健性
           'sec_ret':'Dfactor_calculate.convfactor_calculation(start, end, freq).fbond_return(ND=10)',                     # 10日收益率
           'sec_priceratio_5d':'Dfactor_calculate.convfactor_calculation(start, end, freq).fprice_ratio(ND=5)',            # 5日价格强弱
           'sec_convPremium':'Dfactor_calculate.convfactor_calculation(start, end, freq).fconvPremiumRatio()',             # 转股溢价率
           'sec_modified_premium':'Dfactor_calculate.convfactor_calculation(start, end, freq).fpremium_modified()',        # 修正溢价率
           'sec_double_low':'Dfactor_calculate.convfactor_calculation(start, end, freq).fdouble_low()',                    # 双低
           'sec_amplitude_delta':'Dfactor_calculate.convfactor_calculation(start, end, freq).famplitude_delta()',          # 振幅偏差
           'sec_ret_delta':'Dfactor_calculate.convfactor_calculation(start, end, freq).fprice_delta()',                    # 涨跌幅差
           'sec_stock_mv':'Dfactor_calculate.convfactor_calculation(start, end, freq).fstock_mv()',                        # 正股市值
           'sec_stock_ret_20D':'Dfactor_calculate.convfactor_calculation(start, end, freq).fstock_return(ND=20)',          # 20日正股收益率
           'sec_EP_net_ttm':'Dfactor_calculate.convfactor_calculation(start, end, freq).fEP_net_ttm()',                    # 正股市盈率(归母)
           'sec_EP_deducted_ttm':'Dfactor_calculate.convfactor_calculation(start, end, freq).fEP_deducted_ttm()',          # 正股市盈率(扣非)
           'sec_SUE':'Dfactor_calculate.convfactor_calculation(start, end, freq).fsue(YN=0)',                              # 超预期净利润
           'sec_SUE_after':'Dfactor_calculate.convfactor_calculation(start, end, freq).fsue(YN=1)',                        # 超预期净利润(扣除非经常性损益)
            
           'ts_convprice_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_price(ND=20)',
           'ts_convturnover_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_turnover(ND=20)',
           'ts_volatility5d_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_volatility(MD=5,ND=20)',
           'ts_amplitude_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_amplitude(ND=20)',
           'ts_conv_premium_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_conv_premium(ND=20)',
           'ts_modified_premium_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_modified_premium(ND=20)',
           'ts_double_low_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_double_low(ND=20)',
           'ts_ret_delta_rol20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_ret_delta(ND=20)',
           'ts_bond_premium_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_bond_premium(ND=20)',
           'ts_ret_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_ret_roll(ND=20)',
           'ts_IV_delta_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_IV_delta_roll(ND=20)',
           'ts_IV_BS_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_IV_BS(ND=20)',
           'ts_ytm_roll20d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_ytm(ND=20)',
           'ts_volumerank_roll10d':'Dfactor_calculate.convfactor_calculation(start, end, freq).ts_volume(ND=10)'
           }			
   
    df = eval(lst[factor_name])
    return df


def df_factor_direction(factor_name):  # 获取因子方向
    lst = {"sec_convsize":"-",             # 转债余额
           "sec_convprice":"-",            # 转债价格
           "sec_conv_stocksize":"-",       # 转债余额占正股市值比重
           "sec_ytm":"+",                  # 到期收益率
           "sec_bond_premium": "-",        # 纯债溢价率
           "sec_IV_BS": "-",               # 隐含波动率_BS  
           "sec_IV_delta": "-",            # 隐波差
           "sec_weightskew_63D":"-",       # 63日转债价格偏度因子
           "sec_convturnover":"+",         # 换手率
           "sec_volatility5d":"+",         # 10日波动率
           "sec_conv_amplitude":"+",       # 振幅
           "ts_volumerank_roll10d":"+",    # 成交量分位数，63日分位/10日滚动
           "sec_volstability_10d":"",      # 10日成交量稳健性（备注：不显著）
           "sec_ret":"-",                  # 10日收益率
           "sec_priceratio_5d":"-",        # 5日价格强弱
           "sec_convPremium":"-",          # 转股溢价率
           "sec_modified_premium":"-",     # 修正溢价率
           "sec_double_low":"-",           # 双低
           "sec_amplitude_delta":"+",      # 振幅偏差（备注：不显著）
           "sec_ret_delta":"-",            # 涨跌幅差
           "sec_stock_mv":"-",             # 正股市值（备注：不显著）
           "sec_stock_ret_20D":"+",        # 20日正股收益率
           "sec_EP_net_ttm":"",            # 正股市盈率(归母)（备注：不显著）
           "sec_EP_deducted_ttm":"",       # 正股市盈率(扣非)（备注：不显著）
           "sec_SUE":"+",                  # 超预期净利润
           "sec_SUE_after":"+"}            # 超预期净利润(扣除非经常性损益)
    factor_direction = lst[factor_name]
    return factor_direction


def get_convbond_market(start_date,end_date,tableName,fields=None):
    path = 'http://dataway.hhhstz.com:8888/hsic_base_fmt/cube?'
    tableName = tableName
    query_str = path + 'tableName=' + tableName + '&begDate=' + start_date + '&endDate=' + end_date
    if fields != None: query_str += '&fields=c_code,t_date,' + ','.join(fields)
    data = pd.read_csv(query_str)
    return data

def get_convbond_data(start_date,end_date):
    data = pd.read_csv(r'data/data_all.csv', sep=',', header='infer')
    data['Date'] = pd.to_datetime(data["Date"])
    data = data.sort_values(by="Date")
    data = data[(data.Date >= start_date) & (data.Date <= end_date)]
    return data

 
def get_tradedays():
    wind_engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    sql_str = 'SELECT TRADE_DAYS, S_INFO_EXCHMARKET' + ' FROM ' + 'AShareCalendar' + ' WHERE ' + 'S_INFO_EXCHMARKET = "SSE"'
    tradeD = pd.read_sql(sql_str, wind_engine)
    tradeD['TRADE_DAYS'] = pd.to_datetime(tradeD['TRADE_DAYS'])
    tradeD.sort_values(by='TRADE_DAYS', ascending=True, inplace=True)
    return tradeD

# 基准指数
#def get_index(start, end):
#    data = w.wsd("889033.WI", "close", start, end, "Period=D;PriceAdj=B")
#    df = pd.DataFrame(np.array(data.Data).T, index=data.Times, columns=['benchmark'])
#    return df
def get_index(start, end):
    """
    提取wind转债等权指数作为基准
    """
    rqdatac.init('license', 'AcBHy5_JJ6wjZdu7Q-ey7dX-J3BmyEC_KblY2Q_hBeOuoBaeBbgXTNSe6XZvqKVESbyUf7vMpLLGuO_aqyb3w9fWGI7q4wdClE6cMp_Z3N4PqqTHJ0nr3CIuXtk-5XzSD1p7NTdNcrAfZlRVpMMtY_PDC9FYuXNmC_EnuQg4H-A=fGk9EhHcK3xN189iXYSWLyiMdGUeXXlVZqr2MxhBypSHxQYnIIyxyM8BR8oNnVUdWhKx-ZrFRIjSONd7uYpOvpcBab92P60iAR_JopX61emtrvsY1xG_uCfYhDPBdDSJKaniJhTPuoBIU4JZun8-8fMIxzx7lnwBm2kAUOA_Mpg=')
    data = rqdatac.get_price('866005.RI', start_date=start, end_date=end, frequency='1d', fields=None,
                             adjust_type='none', skip_suspended=False, market='cn', expect_df=True, time_slice=None)
    data = data.reset_index()
    df_index = pd.DataFrame(np.array(data['close'].T), index=data['date'], columns=['benchmark'])
    return df_index


#提取股票相关数据#
def get_stock_data(stock_code, str_date): 
    engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    # A股日行情表 S_DQ_VOLUME：成交量（一手100股）
    query1 = ("""select S_INFO_WINDCODE,TRADE_DT,S_DQ_CLOSE,S_DQ_VOLUME,S_DQ_TRADESTATUS
               from AShareEODPrices
               where S_INFO_WINDCODE in %s and TRADE_DT > '%s'""") % (stock_code, str_date)   
    data1 = pd.read_sql_query(query1, engine).sort_values(by=['S_INFO_WINDCODE','TRADE_DT'])
   
  # A股日行情估值指标 S_VAL_MV总市值
    query2 = ("""select S_INFO_WINDCODE,TRADE_DT,S_VAL_MV
               from AShareEODDerivativeIndicator
               where S_INFO_WINDCODE in %s and TRADE_DT > '%s'""") % (stock_code, str_date) 
    
    data2 = pd.read_sql_query(query2, engine).sort_values(by=['S_INFO_WINDCODE','TRADE_DT'])
    # 获取正股行业代码 	SW_IND_CODE
    query3 = ("""select S_INFO_WINDCODE,SW_IND_CODE
               from AShareSWIndustriesClass
               where S_INFO_WINDCODE in %s""") % (stock_code,)
    data3 = pd.read_sql_query(query3, engine).sort_values(by=['S_INFO_WINDCODE'])
    return data1, data2, data3


#提取行业相关数据#
def get_stock_industry(): 
    engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    # A股日行情表
    query1 = ("""select INDUSTRIESCODE,INDUSTRIESNAME
               from AShareIndustriesCode""")
    data1 = pd.read_sql_query(query1, engine)
    return data1

def get_stock_info(stock_code, str_date): 
    engine = create_engine('mysql+pymysql://hs_wangwenjie:hs_wangwenjie#A0@192.168.201.181:3306/wind?charset=utf8')
    # A股利润表
    query1 = ("""select S_INFO_WINDCODE,ANN_DT,OPER_REV,NET_PROFIT_EXCL_MIN_INT_INC,STATEMENT_TYPE
               from AShareIncome
               where S_INFO_WINDCODE in %s and ANN_DT > '%s'""") % (stock_code,str_date)
    data1 = pd.read_sql_query(query1, engine)
    
    # A股资产负债表
    query2 = ("""select S_INFO_WINDCODE,ANN_DT,TOT_SHRHLDR_EQY_EXCL_MIN_INT
               from AShareBalanceSheet
               where S_INFO_WINDCODE in %s and ANN_DT > '%s'""") % (stock_code,str_date)
    data2 = pd.read_sql_query(query2, engine)
    #审计意见
    query3 = ("""select S_INFO_WINDCODE,ANN_DT,S_STMNOTE_AUDIT_CATEGORY
               from AShareAuditOpinion
               where S_INFO_WINDCODE in %s and ANN_DT > '%s'""") % (stock_code, str_date)
    data3 = pd.read_sql_query(query3, engine)
    return data1, data2, data3


    
    
